package dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import model.Teachers;

public class TeachersMod {
	Connection connection;
	PreparedStatement sql = null;
	private static TeachersMod isntance;
	public static TeachersMod getIsntance() {
		if (isntance == null) {
			isntance = new TeachersMod();
		}
		return isntance;
	}
	public boolean isTID(int tid) throws IOException{ /*kiem tra tinh hop le cua nid va co nam trong DB hay khong*/
		connection =  DB_Connection.getIsntance().getConnection();
		boolean result = false;
		try {
			sql = connection.prepareStatement("SELECT tid FROM teachers where tid = ? ");
			sql.setInt(1, tid);
			ResultSet rs = sql.executeQuery();
			while (rs.next()) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		return result;
	}
	public boolean isExitUidInTable(int uid){
		PreparedStatement sql = null;
		connection =  DB_Connection.getIsntance().getConnection();
		try {
			sql = connection.prepareStatement("SELECT uid FROM teachers where uid = ? ");
			sql.setInt(1, uid);
			ResultSet rs = sql.executeQuery();
			while (rs.next() == true) {
				return true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
			
		}
		return false;
	}
	public boolean addTeacher(Teachers teachers) {
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sqli = "INSERT INTO teachers " +
					"(tid, uid, name, birthday, sex, avatar, email," +
					" address, phone, bio, sjid, qualifications, professional )" +
					" VALUES(? , ? , ? , ? , ? , ? , ? ,? , ? , ? , ? , ? , ? ) ";
			sql = connection.prepareStatement(sqli);
			sql.setInt(1, teachers.getTid());
			sql.setInt(2, teachers.getUid());
			sql.setString(3, teachers.getName());
			sql.setDate(4, teachers.getBirthday());
			sql.setInt(5, teachers.getSex());
			sql.setString(6, teachers.getAvatar());
			sql.setString(7, teachers.getEmail());
			sql.setString(8, teachers.getAddress());
			sql.setString(9, teachers.getPhone());
			sql.setString(10, teachers.getBio());
			sql.setInt(11,teachers.getSjid());
			sql.setString(12, teachers.getQualifications());
			sql.setString(13, teachers.getProfessional());
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpdateTeacherByTid(Teachers teachers, int tid) {
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sqlu = "UPDATE teachers SET  uid = ?, name = ?, birthday = ? , sex = ?, avatar = ? , email = ?," +
					" address = ? , phone = ? , bio = ? , sjid = ?, qualifications = ? , professional = ? WHERE tid = ? LIMIT = 1 ";
			sql = connection.prepareStatement(sqlu);
			sql.setInt(1, teachers.getUid());
			sql.setString(2, teachers.getName());
			sql.setDate(3, teachers.getBirthday());
			sql.setInt(4, teachers.getSex());
			sql.setString(5, teachers.getAvatar());
			sql.setString(6, teachers.getEmail());
			sql.setString(7, teachers.getAddress());
			sql.setString(8, teachers.getPhone());
			sql.setString(9, teachers.getBio());
			sql.setInt(10,teachers.getSjid());
			sql.setString(11, teachers.getQualifications());
			sql.setString(12, teachers.getProfessional());
			sql.setInt(13, tid);
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public boolean UpdateTeacherByUid(Teachers teachers, int uid) {
		PreparedStatement sqlr = null;
		boolean result = false;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			String sqlu = "UPDATE teachers SET  uid = ?, name = ?, birthday = ? , sex = ?, avatar = ? , email = ?," +
					" address = ? , phone = ? , bio = ? , sjid = ?, qualifications = ? , professional = ? WHERE uid = ? LIMIT = 1 ";
			sql = connection.prepareStatement(sqlu);
			sql.setInt(1, teachers.getUid());
			sql.setString(2, teachers.getName());
			sql.setDate(3, teachers.getBirthday());
			sql.setInt(4, teachers.getSex());
			sql.setString(5, teachers.getAvatar());
			sql.setString(6, teachers.getEmail());
			sql.setString(7, teachers.getAddress());
			sql.setString(8, teachers.getPhone());
			sql.setString(9, teachers.getBio());
			sql.setInt(10,teachers.getSjid());
			sql.setString(11, teachers.getQualifications());
			sql.setString(12, teachers.getProfessional());
			sql.setInt(13, uid);
			int number = sql.executeUpdate();
			if (number > 0) {
				result = true;
			}
			sql.close();
			connection.close();
		} catch (Exception e) {
			// TODO: handle exception
		}
		return result;
	}
	public Teachers getTeacherByTid(int t){
		PreparedStatement sqls =null;
		Teachers result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sqls = connection.prepareStatement("SELECT tid, uid, name, birthday, sex, avatar," +
					" email, address, phone, bio, sjid, qualifications, professional " +
					"FROM teachers WHERE tid = ?");
			sqls.setInt(1, t);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				int tid = rs.getInt(1);
				int uid = rs.getInt(2);
				String name = rs.getString(3);
				Date birthday = rs.getDate(4);
				int sex = rs.getInt(5);
				String avatar = rs.getString(6);
				String email = rs.getString(7);
				String address = rs.getString(8);
				String phone = rs.getString(9);
				String bio = rs.getString(10);
				int sjid = rs.getInt(11);
				String qualifications = rs.getString(12);
				String professional = rs.getString(13);
				result = new Teachers(tid, uid, name, birthday, sex, avatar, email, address, phone, bio, sjid, qualifications, professional);
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
		}
		return result;
	 }
	public Teachers getTeacherByUid(int u){
		PreparedStatement sqls =null;
		Teachers result = null;
		try {
			connection = DB_Connection.getIsntance().getConnection();
			sqls = connection.prepareStatement("SELECT tid, uid, name, birthday, sex, avatar," +
					" email, address, phone, bio, sjid, qualifications, professional " +
					"FROM teachers WHERE uid = ?");
			sqls.setInt(1, u);
			ResultSet rs = sqls.executeQuery();
			while (rs.next()) {
				int tid = rs.getInt(1);
				int uid = rs.getInt(2);
				String name = rs.getString(3);
				Date birthday = rs.getDate(4);
				int sex = rs.getInt(5);
				String avatar = rs.getString(6);
				String email = rs.getString(7);
				String address = rs.getString(8);
				String phone = rs.getString(9);
				String bio = rs.getString(10);
				int sjid = rs.getInt(11);
				String qualifications = rs.getString(12);
				String professional = rs.getString(13);
				result = new Teachers(tid, uid, name, birthday, sex, avatar, email, address, phone, bio, sjid, qualifications, professional);
			}
			sqls.close();
			connection.close();
		} catch (Exception e) {
		}
		return result;
	 }
	 public boolean delByTid(int tid){
			PreparedStatement delpd = null;
			boolean result = false;
			Connection connection = DB_Connection.getIsntance().getConnection();
			try {
				delpd = connection.prepareStatement("DELETE FROM teachers WHERE tid = ? LIMIT 1");
				delpd.setInt(1, tid);
				int number = delpd.executeUpdate();
				if (number > 0) {
					result = true;
				}
				delpd.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
				
			}
			return  result;
		}
	 public boolean delByUid(int uid){
			PreparedStatement delpd = null;
			boolean result = false;
			Connection connection = DB_Connection.getIsntance().getConnection();
			try {
				
				delpd = connection.prepareStatement("DELETE FROM teachers WHERE uid = ? LIMIT 1");
				delpd.setInt(1, uid);
				int number = delpd.executeUpdate();
				if (number > 0) {
					result = true;
				}
				delpd.close();
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return  result;
		}
}
